﻿Imports System.Data.OleDb

Public Class frmTheoDoiTon
    Private bEdit As Boolean
    Private Sub frmTheoDoiTon_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            LoaiSanPham_Load()
            cbxLoaiSanPham.SelectedIndex = 1
            'MenuTheoDoiTon

        Catch ex As Exception
            ShowError(ex)
        End Try
    End Sub
#Region "Load loại sản phầm và sản phẩm"
    Private Sub LoaiSanPham_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsLoaiSanPham As New DataSet
            Dim dtLoaiSanPham As New DataTable
            Dim query As String = "Select IDLoai,TenLoai From LoaiSanPham Union Select IdTatCa As IDLoai,TatCa As TenLoai From TatCa"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsLoaiSanPham)
            dbConn.Close()
            dtLoaiSanPham = dsLoaiSanPham.Tables(0)
            cbxLoaiSanPham.DataSource = dtLoaiSanPham
            cbxLoaiSanPham.ValueMember = "IdLoai"
            cbxLoaiSanPham.DisplayMember = "TenLoai"
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        Try
            Dim SoCot As Integer
            If dtpTuNgay.Value.ToShortDateString = dtpDenNgay.Value.ToShortDateString Then
                SoCot = 1
            Else
                SoCot = (dtpDenNgay.Value - dtpTuNgay.Value).Days() + 1
            End If
            'bind data
            dgvTheoDoiTon.DataSource = Nothing
            dgvTheoDoiTon.AutoGenerateColumns = False
            'dgvTheoDoiTon.Rows.Clear()
            dgvTheoDoiTon.ColumnCount = SoCot + 2
            dgvTheoDoiTon.Columns(0).HeaderText = "Mã sản phẩm"
            dgvTheoDoiTon.Columns(0).Name = "MaSanPham"
            dgvTheoDoiTon.Columns(0).DataPropertyName = "MaSanPham"
            dgvTheoDoiTon.Columns(0).Width = 130
            dgvTheoDoiTon.Columns(0).ReadOnly = True
            dgvTheoDoiTon.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
            dgvTheoDoiTon.Columns(1).Name = "TenSanPham"
            dgvTheoDoiTon.Columns(1).HeaderText = "Tên sản phẩm"
            dgvTheoDoiTon.Columns(1).DataPropertyName = "TenSanPham"
            dgvTheoDoiTon.Columns(1).Width = 260
            dgvTheoDoiTon.Columns(1).Frozen = True
            dgvTheoDoiTon.Columns(1).ReadOnly = True
            dgvTheoDoiTon.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable
            Dim newFont As New Font(dgvTheoDoiTon.Font, FontStyle.Underline)
            For i As Integer = 1 To SoCot
                dgvTheoDoiTon.Columns(i + 1).HeaderText = ConvertVietnamese(formatDateVN2String(dtpTuNgay.Value.AddDays(i - 1).ToShortDateString()))
                dgvTheoDoiTon.Columns(i + 1).Width = 80
                dgvTheoDoiTon.Columns(i + 1).DataPropertyName = "Column" & i
                dgvTheoDoiTon.Columns(i + 1).Name = ConvertDateToInt(dtpTuNgay.Value.AddDays(i - 1).ToShortDateString())
                dgvTheoDoiTon.Columns(i + 1).ReadOnly = True
                dgvTheoDoiTon.Columns(i + 1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                dgvTheoDoiTon.Columns(i + 1).DefaultCellStyle.Font = newFont
                dgvTheoDoiTon.Columns(i + 1).SortMode = DataGridViewColumnSortMode.NotSortable
            Next
            'bind data
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim query As String = ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtSoLuongTonKho As New DataTable
            If txtTenSanPham.Text = "" Then
                query = "SELECT MaSanPham,TenSanPham FROM SanPham Where TheoDoiTon=1 And Xoa=0 And IDLoai=" & cbxLoaiSanPham.SelectedValue & " Order By MaSanPham ASC"
            Else
                query = "SELECT MaSanPham,TenSanPham FROM SanPham Where TheoDoiTon=1 And MaSanPham='" & txtTenSanPham.Text & "' And Xoa=0 And IDLoai=" & cbxLoaiSanPham.SelectedValue & " Order By MaSanPham ASC"
            End If

            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtSoLuongTonKho)
            dgvTheoDoiTon.DataSource = dtSoLuongTonKho
            For i As Integer = 1 To SoCot
                dtSoLuongTonKho.Columns.Add("Column" & i)
            Next
            Dim dbCommand As OleDb.OleDbCommand
            Dim NgaySo_HienTai As Integer = ConvertDateToInt(Now.Date.ToShortDateString())
            Dim Ngay_Ton As Integer
            Dim Ton As Integer
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = dtSoLuongTonKho.Rows.Count
            Dim k As Integer = 0
            For Each dr As DataRow In dtSoLuongTonKho.Rows
                For i As Integer = 1 To SoCot
                    'Tính toán số lượng tồn
                    Ngay_Ton = ConvertDateToInt(dtpTuNgay.Value.AddDays(i - 1).ToShortDateString())
                    dbCommand = New OleDbCommand("spTonTheoNgay", dbConn)
                    dbCommand.CommandType = CommandType.StoredProcedure
                    dbCommand.Parameters.Add("@MaSanPham", OleDbType.VarWChar).Value = dr.Item("MaSanPham")
                    dbCommand.Parameters.Add("@NgaSo", OleDbType.Integer).Value = Ngay_Ton
                    Ton = dbCommand.ExecuteScalar()
                    'End If
                    dr.Item("Column" & i) = Format(Ton, "N2")
                Next
                frmBusyLoading.progress.Value = k
                k = k + 1
            Next
            frmBusyLoading.Close()
            dgvTheoDoiTon.DataSource = dtSoLuongTonKho
            dbConn.Close()
        Catch ex As Exception
            ShowError(ex)
        End Try
    End Sub
    Function ConvertVietnamese(ByVal Ngay As String) As String
        Try
            If Ngay = "" Then
                Return ""
            Else
                Dim aNgay() As String = Ngay.Split("/")
                If Integer.Parse(aNgay(0)) <= 9 Then
                    aNgay(0) = "0" & aNgay(0)
                End If
                If Integer.Parse(aNgay(1)) <= 9 Then
                    aNgay(1) = "0" & aNgay(1)
                End If
                Return aNgay(0) & "/" & aNgay(1) & "/" & aNgay(2)
            End If
        Catch ex As Exception

        End Try
    End Function

    Private Sub dgvTheoDoiTon_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTheoDoiTon.CellClick
        Try
            If e.RowIndex >= 0 And e.ColumnIndex > 1 Then
                'Ngay
                'Ma
                frmChiTietXuatNhap.lblNgay.Text = dgvTheoDoiTon.Columns(e.ColumnIndex).HeaderText
                frmChiTietXuatNhap.lblMaSanPham.Text = dgvTheoDoiTon("MaSanPham", e.RowIndex).Value
                frmChiTietXuatNhap.lblTenSanPham.Text = dgvTheoDoiTon("TenSanPham", e.RowIndex).Value
                frmChiTietXuatNhap.lblNgaySo.Text = dgvTheoDoiTon.Columns(e.ColumnIndex).Name
                ShowModalForm(frmChiTietXuatNhap, False, False)
                frmChiTietXuatNhap.lblNgay.Text = ""
                frmChiTietXuatNhap.lblMaSanPham.Text = ""
                frmChiTietXuatNhap.lblTenSanPham.Text = ""
                frmChiTietXuatNhap.lblNgaySo.Text = ""
            End If
        Catch ex As Exception

        End Try
    End Sub


    Private Sub dgvTheoDoiTon_CellMouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvTheoDoiTon.CellMouseMove
        Try
            If e.RowIndex >= 0 And e.ColumnIndex > 1 Then
                dgvTheoDoiTon.Cursor = Cursors.Hand
            Else
                dgvTheoDoiTon.Cursor = Cursors.Default
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmdExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExportExcel.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                ExportExcel(Path, FileName & ".xls")
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
    Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
        Try
            'Dim Start As DateTime = Now
            Dim SubTitle As String = "Từ ngày " & formatDateVN2String(dtpTuNgay.Value.ToShortDateString) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value.ToShortDateString)
            'Dim excelDoc As System.IO.StreamWriter = New System.IO.StreamWriter(FileName)
            Dim startExcelXML As String
            startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
            "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
            "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
            "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
            "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
            "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
             "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
             "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
              "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
              "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
              "<Version>11.9999</Version>" & vbCrLf & _
             "</DocumentProperties>" & vbCrLf & _
             "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
              "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
              "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
              "<WindowTopX>120</WindowTopX>" & vbCrLf & _
              "<WindowTopY>135</WindowTopY>" & vbCrLf & _
              "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
              "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
             "</ExcelWorkbook>" & vbCrLf & _
             "<Styles>" & vbCrLf & _
              "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
               "<Borders/>" & vbCrLf & _
               "<Font/>" & vbCrLf & _
               "<Interior/>" & vbCrLf & _
               "<NumberFormat/>" & vbCrLf & _
               "<Protection/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""s22""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
               "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
               "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
                "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
              "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
             "</Styles>" & vbCrLf & _
             "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
              "<Table>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
               "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BẢNG THEO DÕI TỒN KHO THÀNH PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "1""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row>" & vbCrLf & _
                "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SẢN PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN SẢN PHẨM</Data></Cell>" & vbCrLf
            'GEN COLUM
            For i As Integer = 2 To dgvTheoDoiTon.ColumnCount - 1
                startExcelXML = startExcelXML & "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">" & "Ngày " & dgvTheoDoiTon.Columns(i).HeaderText & "</Data></Cell>"
            Next
                startExcelXML = startExcelXML & "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
               "</Row>"
            'write
            ExportToFile(Path, FileName, startExcelXML)
            Dim contentExcelXML As String = ""
            'contentExcelXML = DevideRowGrid()
            Dim EndRow As Integer = dgvTheoDoiTon.RowCount - 1
            Dim StartRow As Integer = 0
            Dim K As Integer = EndRow \ 100
            Dim SoDu As Integer = EndRow Mod 100
            Dim aK(100) As Integer
            For i As Integer = 1 To 100
                If K * i <= EndRow Then
                    aK(i) = K * i
                End If
            Next
            aK(100) = 100 * K + SoDu
            Dim sRet As String = ""
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = 100
            'firtrow
            sRet = ConvertExcel(0, 0)
            If sRet <> "" Then
                ExportToFile(Path, FileName, sRet)
            End If
            'continue
            For i As Integer = 0 To 99
                sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                If sRet <> "" Then
                    ExportToFile(Path, FileName, sRet)
                End If
                frmBusyLoading.progress.Value = i
            Next
            frmBusyLoading.Close()
            Dim endExcelXML As String
            endExcelXML = "</Table>" & vbCrLf & _
              "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
               "<Print>" & vbCrLf & _
                "<ValidPrinterInfo/>" & vbCrLf & _
                "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
               "</Print>" & vbCrLf & _
               "<Selected/>" & vbCrLf & _
                  "<FreezePanes/>" & vbCrLf & _
                " <FrozenNoSplit/>" & vbCrLf & _
               "<SplitHorizontal>6</SplitHorizontal>" & vbCrLf & _
               "<TopRowBottomPane>6</TopRowBottomPane>" & vbCrLf & _
               "<SplitVertical>2</SplitVertical>" & vbCrLf & _
               "<LeftColumnRightPane>2</LeftColumnRightPane>" & vbCrLf & _
               "<ActivePane>0</ActivePane>" & vbCrLf & _
               "<Panes>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>3</Number>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>1</Number>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>2</Number>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>0</Number>" & vbCrLf & _
                 "<ActiveRow>5</ActiveRow>" & vbCrLf & _
                 "<ActiveCol>1</ActiveCol>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
               "</Panes>" & vbCrLf & _
               "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
               "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
              "</WorksheetOptions>" & vbCrLf & _
             "</Worksheet>" & vbCrLf & _
            "</Workbook>"
            ExportToFile(Path, FileName, endExcelXML)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
        Try
            Dim contentExcelXML As String = ""
            For i As Integer = iBegin To iEnd
                contentExcelXML = contentExcelXML & "<Row>"
                For j As Integer = 0 To dgvTheoDoiTon.ColumnCount - 1
                    If j = 0 Or j = 1 Then
                        contentExcelXML = contentExcelXML & "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvTheoDoiTon(j, i).Value & "</Data></Cell>"
                    Else
                        contentExcelXML = contentExcelXML & "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & dgvTheoDoiTon(j, i).Value & "</Data></Cell>"
                    End If
                Next
                contentExcelXML = contentExcelXML & "</Row>"
            Next
            
            'For i As Integer = iBegin To iEnd
            '    If Grid1(i, 0).ColumnSpan = 9 Then
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '                                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 0).Value & "</Data></Cell>" & vbCrLf & _
            '                            "</Row>"
            '    Else
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 2).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 3).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 4).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 6).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 7).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 8).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '   "</Row>"
            '    End If
            '    'frmBusyLoading.progress.Value = i
            'Next
            Return contentExcelXML
        Catch ex As Exception

        End Try
    End Function
#Region "Tìm kiếm sản phẩm"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String 
            If cbxLoaiSanPham.SelectedValue = 0 Then
                query = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.TheoDoiTon=1 And SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Else
                query = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                   " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                   " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                   " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                   " Where SanPham.TheoDoiTon=1 And SanPham.IDLoai=" & cbxLoaiSanPham.SelectedValue & " And SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
                cmdThongKe.Focus()
            End If
        Catch ex As Exception

        End Try
    End Sub



    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    cmdThongKe.Focus()
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
End Class